package database;

import database.common.ConnectionFactory;
import model.KostoType;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

/**
 * Created with IntelliJ IDEA.
 * Author: Kostika Gorica
 * User:   kosta
 * Date:   3/10/13
 * Time:   10:33 PM
 */
public class KostoTypeDao {

    private static final String INSERT_KOSTO_TYPE =
            "INSERT INTO public.kosto_type ( emri ) VALUES (?)";
    private static final String UPDATE_KOSTO_TYPE =
            "UPDATE public.kosto_type SET emri=? WHERE id=? ";
    private static final String DELETE_KOSTO_TYPE =
            "DELETE FROM public.kosto_type WHERE id = ?";
    private static final String SELECT_SINGLE_KOSTO_TYPE =
            "SELECT id, emri FROM public.kosto_type WHERE id=?";
    private static final String SELECT_ALL_KOSTO_TYPES =
            "SELECT * from query_to_xml('SELECT * FROM public.kosto_type',true,false,'')";


    public static int insert(KostoType kostoType){
        Connection connection = null;
        PreparedStatement statement = null;
        int rows = 0;
        try {
            connection = ConnectionFactory.getConnection();
            statement = connection.prepareStatement(INSERT_KOSTO_TYPE);
            statement.setString(1, kostoType.getEmri());
            rows = statement.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try{
                if (statement!=null) statement.close();
                if (connection!=null) connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return rows;
    }

    public static int update(KostoType kostoType){
        Connection connection = null;
        PreparedStatement statement = null;
        int rows = 0;
        try {
            connection = ConnectionFactory.getConnection();
            statement = connection.prepareStatement(UPDATE_KOSTO_TYPE);
            statement.setString(1, kostoType.getEmri());
            statement.setInt(2, kostoType.getId());
            rows = statement.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try{
                if (statement!=null) statement.close();
                if (connection!=null) connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return rows;
    }

    public static int delete(int id){
        Connection connection = null;
        PreparedStatement statement = null;
        int rows = 0;
        try {
            connection = ConnectionFactory.getConnection();
            statement = connection.prepareStatement(DELETE_KOSTO_TYPE);
            statement.setInt(1, id);
            rows = statement.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try{
                if (statement!=null) statement.close();
                if (connection!=null) connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return rows;
    }

    public static KostoType selectKostoType(int id){
        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        KostoType kostoType = null;
        try {
            connection = ConnectionFactory.getConnection();
            statement = connection.prepareStatement(SELECT_SINGLE_KOSTO_TYPE);
            statement.setInt(1, id);
            resultSet = statement.executeQuery();
            if (resultSet.next()){
                kostoType = new KostoType();
                kostoType.setId(resultSet.getInt(1));
                kostoType.setEmri(resultSet.getString(2));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try{
                if (resultSet!=null) resultSet.close();
                if (statement!=null) statement.close();
                if (connection!=null) connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return kostoType;
    }

    public static String selectAllKostoTypes(){
        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        String result = "";
        try {
            connection = ConnectionFactory.getConnection();
            statement = connection.prepareStatement(SELECT_ALL_KOSTO_TYPES);
            resultSet = statement.executeQuery();
            if (resultSet.next()){
                result=resultSet.getString(1);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try{
                if (resultSet!=null) resultSet.close();
                if (statement!=null) statement.close();
                if (connection!=null) connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return result;
    }

}
